import python_basic.common.xdb as xdb
import pymysql
from python_basic.common.xcommon import xshow_table, sep, xcheck

with pymysql.connect(**xdb.xdb_args) as xconn:
    with xconn.cursor() as xcursor:


        def xselect_users():
            xsql = "SELECT * FROM `users`"
            sep(xsql)
            xrows = xdb.xquery(xcursor, xsql)
            xrows_len = len(xrows)
            if 0 == xrows_len:
                print('No records found.')
            else:
                xshow_table(xrows)
            return xrows_len


        xselect_users()


        def xinsert_user(*args):
            xsql = "INSERT INTO `users` VALUES "
            xfirst = True
            for xe in args:
                if xfirst:
                    xfirst = False
                else:
                    xsql += ', '
                xsql += f"(NULL, '{xe[0]}', '{xe[1]}')"
            sep(xsql)
            xcursor.execute(xsql)
            xconn.commit()


        xinsert_user(('裴Pei 大狗Big Dog', '666'), ('吴Wu 大棉Big Sheep', '7777'))
        xselect_users()

        xsql = "SELECT * FROM `users` LIMIT 5"
        sep(xsql)
        xcursor.execute(xsql)
        while True:
            xrows = xcursor.fetchmany(2)
            if 0 == len(xrows):
                break
            xshow_table(xrows)

        sep(xsql)
        xcursor.execute(xsql)
        while True:
            xrow = xcursor.fetchone()
            if xrow is None:
                break
            xshow_table((xrow,))

        def xdel_user(xid):
            try:
                xid = int(xid)
                tup = (xid,)
                xsql = f"DELETE FROM `users` WHERE id = %s"
                sep(xsql + ': ' + ', '.join([str(x) for x in tup]))
                xcursor.execute(xsql, tup)
                xconn.commit()
            except ValueError as ex:
                print(repr(ex))


        def xget_2_ids():
            xsql = "SELECT id FROM `users` ORDER BY id DESC LIMIT 2, 2"
            sep(xsql)
            xcursor.execute(xsql)
            xline01, xline02 = xcursor.fetchmany(2)
            return int(xline01[0]), int(xline02[0])


        id01, id02 = xget_2_ids()
        xdel_user('a')
        xdel_user(id01)
        xdel_user(id02)
        xselect_users()


        def xupdate_user(xid, xname=None, xpwd=None):
            try:
                xid = int(xid)
                xlist = []
                if xname is not None:
                    xsub_sql = f" `name` = %(name)s "
                    xlist.append(xsub_sql)
                if xpwd is not None:
                    xsub_sql = f" `password` = %(pwd)s "
                    xlist.append(xsub_sql)
                if 0 == len(xlist):
                    print(f'Noting to do with id {xid}')
                    return
                xsub_sql_cat = ', '.join(xlist)  # Intended error: , => ,,
                arg_dict = dict(id=xid, name=xname, pwd=xpwd)
                try:
                    xsql = f"UPDATE `users` SET {xsub_sql_cat} WHERE ID = %(id)s LIMIT 1"
                    sep(xsql + ':' + repr(arg_dict))
                    r = xcursor.execute(xsql, arg_dict)
                    xconn.commit()
                    return r
                except Exception as ex:
                    xconn.rollback()
                    print(repr(ex))
            except ValueError as ex:
                print(repr(ex))


        import string, random

        print(xupdate_user(1, xpwd=''.join(random.choices(string.ascii_uppercase + string.digits, k=6))))
        print(xupdate_user(2))
        print(xupdate_user(3, 'Zsj', 'Ily'))
        print(xupdate_user(4, 'Zsj vs. Yt'))
        xselect_users()
